﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Reflection;
using System.Windows.Forms;

using System.Drawing;

using Excel = Microsoft.Office.Interop.Excel;


namespace Common
{
    /// <summary>
    /// sheet表属性
    /// </summary>


    public class ExcelHelper
    {
        #region Model
        enum ErrExcelHelper
        {
            请选择_Excel_文件,
            Sheet_表不存在,
            数据表更新失败,
            success
        }
        public class SheetProp
        {
            public string 名称 { get; set; }
            public string 类型 { get; set; }
            public DateTime 建立时间 { get; set; }
            public DateTime 修改时间 { get; set; }
        }

        public class SheetColumn
        {
            public string ColumnHeader { get; set; }
            public List<string> ColumnValueList { get; set; } = new List<string>();
        }
        #endregion

        [System.Runtime.InteropServices.DllImport("User32.dll")]

        private static extern int GetWindowThreadProcessId(IntPtr hWnd, out int ProcessId);

        private static void KillExcel(Excel.Application xlsApp)
        {
            int id = 0;
            IntPtr intptr = new IntPtr(xlsApp.Hwnd);
            System.Diagnostics.Process p = null;
            try
            {
                GetWindowThreadProcessId(intptr, out id);
                p = System.Diagnostics.Process.GetProcessById(id);
                if (p.ProcessName == "EXCEL")
                {
                    p.Kill();
                    //p.Dispose();
                }
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// 由 EXCEL表 转换成DataTable，不受Excel文件是否打开的影响
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">数据表名称</param>
        /// <returns></returns>
        public DataTable GetDataTableFromSheet(String fileName, string sheetName)
        {
            if (fileName == null || fileName == "") { return null; }
            if (sheetName == null || sheetName == "") { return null; }

            string strConn;

            //IMEX=1的时候并不是全都会作为字符串来处理，根据系统的默认设置，通常如果前8行有字符串，则该列会作为字符串来处理，如果全都为数字，则该列为数字列，日期也是一样。

            //如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/ TypeGuessRows，如果此值为0，
            //则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少。
            strConn = string.Format($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter($"SELECT * FROM [{sheetName}$]", strConn);
            DataTable dt = new DataTable();

            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable表保存为Excel文件
        /// </summary>
        /// <param name="dt">DataTable表</param>
        /// <param name="filename">Excel文件全名</param>
        public void DataTableSaveToExcel(System.Data.DataTable dt, string filename)
        {
            if (System.IO.File.Exists(filename))
            { System.IO.File.Delete(filename); }

            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;
            try
            {
                xlsApp = new Excel.Application();

                xlsBook = xlsApp.Workbooks.Add();
                xlsSheet = (Excel.Worksheet)xlsBook.Worksheets[1];
                xlsSheet.Name = dt.TableName;

                Clipboard.Clear();
                // 放入剪切板
                Clipboard.SetDataObject(DataTableToString(dt), false);
                Excel.Range range = (Excel.Range)xlsSheet.Cells[1, 1];
                xlsSheet.Activate();
                range.Select();
                xlsSheet.Paste();
                // 清空剪切板
                Clipboard.Clear();
                range.Select();
                xlsBook.SaveAs(filename);
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }

                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }
        }
        public string DataTableToCilpBoard(DataTable dt)
        {
            try
            {
                Clipboard.SetDataObject(DataTableToString(dt), false);
                return "success";
            }
            catch (Exception Ex)
            {
                return Ex.Message;
            }
        }

        /// <summary>
        /// 在指定的Excel文件新建Sheet表
        /// </summary>
        /// <param name="fileName">Excel文件全名</param>
        /// <param name="sheetName">Sheet表名</param>
        public void CreSheet(String fileName, string sheetName)
        {
            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;

            try
            {
                xlsApp = new Excel.Application();
                xlsApp.DisplayAlerts = false;

                xlsBook = xlsApp.Workbooks.Open(fileName, UpdateLinks: 0);
                Excel.Worksheet st = xlsBook.Sheets[xlsBook.Sheets.Count];
                xlsSheet = xlsBook.Sheets.Add(After: st);
                xlsSheet.Name = sheetName;
                xlsBook.Save();

                xlsBook.Close();
                xlsApp.DisplayAlerts = true;
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }
                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }
        }

        /// <summary>
        /// 删除Excel文件Sheet表
        /// </summary>
        /// <param name="fileName">Excel文件全名</param>
        /// <param name="sheetName">Sheet表名</param>
        /// <returns>成功返回"success",失败返回"fail"</returns>
        public string DelSheet(String fileName, string sheetName)
        {
            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;

            try
            {
                xlsApp = new Excel.Application();

                xlsBook = xlsApp.Workbooks.Open(fileName, UpdateLinks: 0);
                xlsSheet = (Excel.Worksheet)xlsBook.Worksheets[sheetName];
                xlsSheet.Delete();
                xlsBook.Save();
                return "success";
            }
            catch (Exception)
            {
                return "false";
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }

                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }
        }

        /// <summary>
        /// 清除Excel文件Sheet表的内容
        /// </summary>
        /// <param name="fileName">Excel文件全名</param>
        /// <param name="sheetName">Sheet表名</param>
        public void ClearSheet(String fileName, string sheetName)
        {
            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;
            try
            {
                xlsApp = new Excel.Application();

                xlsBook = xlsApp.Workbooks.Open(fileName, UpdateLinks: 0);
                xlsSheet = (Excel.Worksheet)xlsBook.Worksheets[sheetName];
                xlsSheet.Cells.Clear();
                xlsBook.Save();
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }

                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }
        }

        /// <summary>
        /// 验证指定的Sheet文件是否存在
        /// </summary>
        /// <param name="fileName">Excel文件全名</param>
        /// <param name="sheetName">Sheet表名</param
        public bool IsSheetExist(String fileName, string sheetName)
        {

            if (fileName == null || fileName == "") { return false; }
            if (sheetName == null || sheetName == "") { return false; }

            string strConn;

            //IMEX=1的时候并不是全都会作为字符串来处理，根据系统的默认设置，通常如果前8行有字符串，则该列会作为字符串来处理，如果全都为数字，则该列为数字列，日期也是一样。

            //如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/ TypeGuessRows，如果此值为0，
            //则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少。
            strConn = string.Format($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand($"SELECT top 1 * FROM [{sheetName}$]", conn);
            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 将DataTable转换为带制表符的字符串
        /// </summary>
        /// <param name="dt">DataTable表</param>
        /// <param name="header">是否包含表头</param>
        /// <returns>返回 String 字符串</returns>
        public string DataTableToString(DataTable dt, bool header = true)
        {
            StringBuilder buffer = new StringBuilder();

            // Excel中列与列之间按照Tab隔开
            if (header)
            {
                foreach (DataColumn col in dt.Columns)
                { buffer.Append(col.ColumnName + "\t"); }
                buffer.Remove(buffer.Length - 1, 1);
                buffer.AppendLine();
            }

            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn col in dt.Columns)
                { buffer.Append(row[col].ToString() + "\t"); }
                buffer.Remove(buffer.Length - 1, 1);
                buffer.AppendLine();
            }
            return buffer.ToString();
        }

        /// <summary>
        /// DataTable更新Sheet数据（会把相应区域内的原有数据覆盖）
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="fileName">Excel文件全名</param>
        /// <param name="sheetName">Sheet表名</param>
        /// <param name="rowNo">行号从1开始</param>
        /// <param name="colNo">列号从1开始</param>
        /// <param name="header">是否包含表头，默认包含</param>
        /// <param name="IsClear">是否删除表格中原有内容</param>
        public string DataTalbeUpdateSheet(DataTable dt, String fileName, string sheetName,
            int rowNo = 1, int colNo = 1, bool header = true, bool IsClear = true)
        {
            string result = "";

            if (System.IO.File.Exists(fileName) == false)
            { return ErrExcelHelper.请选择_Excel_文件.ToString(); }

            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;
            try
            {
                xlsApp = new Excel.Application();
                xlsBook = xlsApp.Workbooks.Open(fileName, UpdateLinks: 0);
                try
                {
                    xlsSheet = (Excel.Worksheet)xlsBook.Worksheets[sheetName];
                }
                catch (Exception)
                {
                    result = ErrExcelHelper.Sheet_表不存在.ToString();
                    goto End;
                }
                xlsApp.DisplayAlerts = false;

                if (IsClear == true)
                {
                    xlsSheet.Cells.Clear();
                }

                Clipboard.Clear();
                // 放入剪切板
                Clipboard.SetDataObject(DataTableToString(dt, header), false);
                Excel.Range range = (Excel.Range)xlsSheet.Cells[rowNo, colNo];
                xlsSheet.Activate();
                range.Select();
                xlsSheet.Paste();
                SetBorder(xlsApp.Selection);

                // 清空剪切板
                Clipboard.Clear();
                range.Select();
                xlsBook.Save();
                xlsApp.DisplayAlerts = true;

                result = ErrExcelHelper.success.ToString();
            }
            catch (Exception)
            {
                result = ErrExcelHelper.数据表更新失败.ToString();
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }

                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }

            End:
            return result;
        }

        /// <summary>
        /// 获取Excel文件的Sheet属性
        /// </summary>
        /// <param name="fileName">Excel全文件名</param>
        /// <returns></returns>
        public List<SheetProp> GetSheetProp(String fileName)
        {
            if (fileName == null || fileName == "") { return null; }

            string strConn;

            //IMEX=1的时候并不是全都会作为字符串来处理，根据系统的默认设置，通常如果前8行有字符串，则该列会作为字符串来处理，如果全都为数字，则该列为数字列，日期也是一样。

            //如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/ TypeGuessRows，如果此值为0，
            //则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少。
            strConn = string.Format($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            DataTable dt = new DataTable();
            conn.Open();
            dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            conn.Close();

            List<SheetProp> sheetPropList = new List<SheetProp>();
            SheetProp objSheetProp = new SheetProp();
            foreach (DataRow item in dt.Rows)
            {
                objSheetProp = new SheetProp();
                objSheetProp.名称 = item["TABLE_NAME"].ToString();
                objSheetProp.名称 = objSheetProp.名称.Substring(0, objSheetProp.名称.Length - 1);
                objSheetProp.类型 = item["TABLE_TYPE"].ToString();
                objSheetProp.建立时间 = Convert.ToDateTime(item["DATE_CREATED"].ToString());
                objSheetProp.修改时间 = Convert.ToDateTime(item["DATE_MODIFIED"].ToString());
                sheetPropList.Add(objSheetProp);
            }
            return sheetPropList;
        }

        /// <summary>
        /// 设置数据列的数据属性。对于不符合数据属性的数据，数值返回 0 ；日期返回 new DateTime ；布尔型 返回 false ,结果返回数据表
        /// </summary>
        /// <param name="datatableOri">原始数据表</param>
        /// <param name="columnDataType">数据类型字典（数据名，数据类型）</param>
        /// <returns></returns>
        public DataTable SetColumnDataType(DataTable datatableOri, Dictionary<string, string> columnDataType)
        {
            DataTable dtResult = new DataTable();
            dtResult = datatableOri.Clone(); //拷贝数据结构

            foreach (DataColumn item in dtResult.Columns)
            {
                try
                {
                    string strDataType = columnDataType[item.ColumnName];
                    switch (strDataType)
                    {
                        case "String":
                            item.DataType = Type.GetType("System.String");
                            break;
                        case "Double":
                            item.DataType = Type.GetType("System.Double");
                            break;
                        case "DateTime":
                            item.DataType = Type.GetType("System.DateTime");
                            break;
                        case "Bool":
                            item.DataType = Type.GetType("System.Boolean");
                            break;
                    }
                }
                catch (Exception) { }
            }

            DataRow errRow = dtResult.NewRow();//出错行逐个赋值
            foreach (DataRow row in datatableOri.Rows)
            {
                try
                {
                    dtResult.ImportRow(row);
                }
                catch (Exception)
                {
                    errRow = dtResult.NewRow();
                    foreach (DataColumn col in dtResult.Columns)
                    {
                        if (col.DataType == typeof(double))
                        {
                            if (row[col.ColumnName] == null || row[col.ColumnName].ToString().Trim().Length == 0)
                            {
                                errRow[col.ColumnName] = 0;
                            }
                            else
                            {
                                errRow[col.ColumnName] = row[col.ColumnName];
                            }
                        }
                        else if (col.DataType == typeof(DateTime))
                        {
                            if (row[col.ColumnName] == null || row[col.ColumnName].ToString().Trim().Length == 0)
                            {
                                errRow[col.ColumnName] = new DateTime();
                            }
                            else
                            {
                                errRow[col.ColumnName] = row[col.ColumnName];
                            }
                        }
                        else if (col.DataType == typeof(bool))
                        {
                            if (row[col.ColumnName] == null || row[col.ColumnName].ToString().Trim().Length == 0)
                            {
                                errRow[col.ColumnName] = false;
                            }
                            else
                            {
                                errRow[col.ColumnName] = row[col.ColumnName];
                            }
                        }
                        else
                        {
                            errRow[col.ColumnName] = row[col.ColumnName];
                        }
                    }
                    dtResult.Rows.Add(errRow);
                }
            }

            return dtResult;
        }

        public List<SheetColumn> GetSheetColumnList(String fileName, string sheetName)
        {
            List<SheetColumn> resultList = new List<SheetColumn>();
            SheetColumn objResult = new SheetColumn();

            Excel.Application xlsApp = null;
            Excel.Workbook xlsBook = null;
            Excel.Worksheet xlsSheet = null;

            Excel.Range rangeColumnHeader = null;

            try
            {
                xlsApp = new Excel.Application();
                xlsBook = xlsApp.Workbooks.Open(fileName, UpdateLinks: 0);
                try
                {
                    xlsSheet = (Excel.Worksheet)xlsBook.Worksheets[sheetName];
                }
                catch (Exception)
                {
                    return new List<SheetColumn>();
                }
                xlsApp.DisplayAlerts = false;

                rangeColumnHeader = xlsSheet.Range["1:1"].SpecialCells(Excel.XlCellType.xlCellTypeConstants);
                foreach (Excel.Range rangeItem in rangeColumnHeader)
                {
                    objResult = new SheetColumn();
                    objResult.ColumnHeader = rangeItem.Value;
                    foreach (Excel.Range item in rangeItem.EntireColumn.SpecialCells(Excel.XlCellType.xlCellTypeConstants))
                    {
                        if (item.Row > 1)
                        {
                            objResult.ColumnValueList.Add(Convert.ToString(item.Value));
                        }
                    }
                    resultList.Add(objResult);
                }
                xlsApp.DisplayAlerts = true;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                try
                {
                    xlsBook.Close(SaveChanges: false);
                }
                catch (Exception)
                {
                }

                if (xlsApp != null)
                {
                    xlsApp.Quit();
                }

                KillExcel(xlsApp);
            }

            return resultList;
        }

        #region 设置单元格格式
        public void SetBorder(Excel .Range selectedRange)
        {

            selectedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous ;
            selectedRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
        }

        #endregion
    }
}

